向组合框添加唯一的值列表 |
您所在的位置：网站首页 › excel combo › 向组合框添加唯一的值列表 |
|
向组合框添加唯一的值列表
项目
04/07/2023
这些示例展示了从电子表格中获取列表，并仅使用唯一值填充组合框控件的不同方法。 第一个示例使用 Range 对象的 AdvancedFilter 方法，第二个示例使用 Collection 对象。 示例代码提供者： Dennis Wallentin， VSTO & .NET & Excel Sub Populate_Combobox_Worksheet() 'The Excel workbook and worksheets that contain the data, as well as the range placed on that data Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnData As Range 'Variant to contain the data to be placed in the combo box. Dim vaData As Variant 'Initialize the Excel objects Set wbBook = ThisWorkbook Set wsSheet = wbBook.Worksheets("Sheet1") 'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column. With wsSheet Set rnData = .Range(.Range("A1"), .Range("A100").End(xlUp)) rnData.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=.Range("L1"), _ Unique:=True 'store the unique values in vaData vaData = .Range(.Range("L2"), .Range("L100").End(xlUp)).Value 'clean up the contents of the temporary data storage .Range(.Range("L1"), .Range("L100").End(xlUp)).ClearContents End With 'display the unique values in vaData in the combo box already in existence on the worksheet. With wsSheet.OLEObjects("ComboBox1").Object .Clear .List = vaData .ListIndex = -1 End With End Sub Sub Populate_Combobox_Worksheet_Collection() 'The Excel workbook and worksheets that contain the data, as well as the range placed on that data Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnData As Range Dim vaData As Variant 'the list, stored in a variant Dim ncData As New VBA.Collection 'the list, stored in a collection Dim lnCount As Long 'the count used in the On Error Resume Next loop. Dim vaItem As Variant 'a variant representing the type of items in ncData 'Instantiate the Excel objects. Set wbBook = ThisWorkbook Set wsSheet = wbBook.Worksheets("Sheet2") 'Using Sheet2,retrieve the range of the list in Column A. With wsSheet Set rnData = .Range(.Range("A2"), .Range("A100").End(xlUp)) End With 'Place the list values into vaData. vaData = rnData.Value 'Place the list values from vaData into the VBA.Collection. On Error Resume Next For lnCount = 1 To UBound(vaData) ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1)) Next lnCount On Error GoTo 0 'Clear the combo box (in case you ran the macro before), 'and then add each unique variant item from ncData to the combo box. With wsSheet.OLEObjects("ComboBox1").Object .Clear For Each vaItem In ncData .AddItem ncData(vaItem) Next vaItem End With End Sub 关于参与者
Dennis Wallentin 是 VSTO & .NET & Excel（专门介绍适用于 Excel 和 Excel Services 的 .NET Framework 解决方案的博客）的作者。 Dennis 已经从事 Excel 解决方案开发超过 20 年，同时也是“专业 Excel 开发：使用 Microsoft Excel、VBA 和 .NET 开发应用程序的权威指南（第 2 版）”的合著者。 支持和反馈有关于 Office VBA 或本文档的疑问或反馈？ 请参阅 Office VBA 支持和反馈，获取有关如何接收支持和提供反馈的指南。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |